package com.lm.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.lm.dao.SalesEventDAO;
import com.lm.model.Salesevent;
import com.lm.model.Taocan;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.util.DBUtil;

public class SalesEventDAOImpl implements SalesEventDAO {
	private ComboPooledDataSource dataSource;

	public void setDataSource(ComboPooledDataSource dataSource) {
		this.dataSource = dataSource;
	}

	public List getAllEventsList() throws Throwable {
		List resList = new ArrayList();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append(" select * from lm_salesevent");
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				Salesevent se = new Salesevent();

				se.setId(rs.getInt("id"));
				se.setEventName(rs.getString("eventName"));
				se.setEventDesc(rs.getString("eventDesc"));
				se.setEventPicUrl(rs.getString("eventPicURL"));
				se.setEventPicName(rs.getString("eventPicName"));

				resList.add(se);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return resList;
	}

	public List getAllTaoCanList(String eventID) throws Throwable {
		List resList = new ArrayList();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();

			sb
					.append(" select id,TaoCanName,totalMarketBid,totalCardBid,cardMinux");
			sb.append(" from lm_taocan ");
			sb.append(" where eventID=").append(Integer.parseInt(eventID));

			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				Taocan tc = new Taocan();
				tc.setId(rs.getInt("id"));
				tc.setTaoCanName(rs.getString("TaoCanName"));
				tc.setTotalMarketBid(rs.getDouble("totalMarketBid"));
				tc.setTotalCardBid(rs.getDouble("totalCardBid"));
				tc.setCardMinux(rs.getDouble("cardMinux"));

				resList.add(tc);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return resList;
	}

	public void addEvent(Salesevent salesEvent) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();
		sb
				.append(" insert into lm_salesevent(eventName,eventDesc,eventPicName,eventPicURL)");

		sb.append(" values('").append(salesEvent.getEventName()).append("',");
		sb.append("'").append(salesEvent.getEventDesc()).append("',");
		sb.append("'").append(salesEvent.getEventPicName()).append("',");
		sb.append("'").append(salesEvent.getEventPicUrl()).append("')");
		try {
			con = dataSource.getConnection();
			con.setAutoCommit(false);
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate();

			con.commit();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

	}

	public void editEvent(Salesevent salesEvent, String eventID)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		StringBuilder sb = new StringBuilder();

		sb.append("update lm_salesevent set eventName='").append(
				salesEvent.getEventName());
		sb.append("',eventDesc='").append(salesEvent.getEventDesc());
		sb.append("',eventPicName='").append(salesEvent.getEventPicName());
		sb.append("',eventPicURL='").append(salesEvent.getEventPicUrl())
				.append("'");
		sb.append(" where id=").append(Integer.parseInt(eventID));
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	public Salesevent getEventByID(String eventID) throws Throwable {
		Salesevent se = new Salesevent();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append(" select * from lm_salesevent  ");
		sb.append(" where id=").append(Integer.parseInt(eventID));
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				se.setEventDesc(rs.getString("eventDesc"));
				se.setEventName(rs.getString("eventName"));
				se.setEventPicName(rs.getString("eventPicName"));
				se.setEventPicUrl(rs.getString("eventPicURL"));
				se.setId(rs.getInt("id"));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
		return se;
	}

	public void deleteEventByEventID(String eventID) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		StringBuilder sb = new StringBuilder();
		sb.append(" delete from lm_salesevent ");
		sb.append(" where id=").append(Integer.parseInt(eventID));
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

	}
}
